﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using PM.Entity;
using System.Data.OracleClient;

namespace PM.Services
{
    public static class BankServices
    {
        private static Bank GetBankFromDataReader(IDataReader dataReader)
        {
            Bank retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Bank();
                retVal.BankId = dataReader["BANK_ID"].ToString();
                retVal.Name = dataReader["BANK_NAME"].ToString();
                retVal.Branch = dataReader["BRANCH"].ToString();
            }
            return retVal;
        }

        public static BankCollection GetAllBankCollectionWithBranch()
        {
            BankCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select Bank_Id, Bank_Name || '-' || Branch as \"BankName\" from Bank";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new BankCollection();
                    while (dataReader.Read())
                    {
                        Bank bank = new Bank();
                        bank.BankId = dataReader["Bank_Id"].ToString();
                        bank.Name = dataReader["BankName"].ToString();
                        retVal.Add(bank);                        
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static BankCollection GetBankCollection()
        {
            BankCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new BankCollection();
                    while (dataReader.Read())
                    {
                        Bank item = GetBankFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static Bank GetBankById(string bankId)
        {
            Bank retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from BANK where BANK_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = bankId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new Bank();
                    if (dataReader.Read())
                    {
                        retVal = GetBankFromDataReader(dataReader);                        
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Bank bank)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into BANK(BANK_ID, BANK_NAME, BRANCH)";
                cmd.CommandText += "values(:v1,:v2,:v3)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = bank.BankId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = bank.Name;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = bank.Branch;              
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Bank bank)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();

                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update BANK set " +
                                  "BANK_NAME = :v2, " +
                                  "BRANCH = :v3";
                cmd.CommandText += " where BANK_ID = :v1";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = bank.BankId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = bank.Name;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = bank.Branch;               

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string bank_id)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from BANK where BANK_ID = '" + bank_id + "'";
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
